﻿using BLL;
using BLL.Access;
using DevExpress.Web;
using Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Web.Class;
using System.Data.OleDb;

namespace Web.Pages.QuShui.DiXiaS.ShouFeiGL
{
    public partial class HuiGuanTF_DR : BasePage
    {
        #region 属性

        /// <summary>
        /// 保存结果集1
        /// </summary>
        private DataTable dtGrid
        {
            get
            {
                return ViewState["dtGrid"] as DataTable;
            }
            set
            {
                ViewState["dtGrid"] = value;
            }
        }

        private DataTable dtGrid2
        {
            get
            {
                return ViewState["dtGrid2"] as DataTable;
            }
            set
            {
                ViewState["dtGrid2"] = value;
            }
        }

        #endregion

        #region 变量

        /// <summary>
        /// 数据访问操作类
        /// </summary>
        SpHelper _SpHelper = new SpHelper();

       
        /// <summary>
        /// 操作日志BLL
        /// </summary>
        SYS_LOGBLL _SYS_LOGBLL = new SYS_LOGBLL();
        #endregion

        #region 事件

        /// <summary>
        /// 页面加载事件
        /// </summary>
        protected void Page_Load(object sender, EventArgs e)
        {
            //第一次页面加载
            if (!IsPostBack)
            {
                InitData();
                LoadGridData();
            }
            //ScriptManager1.RegisterPostBackControl(btnExport);
            gvList.DataSource = dtGrid;
            gvList.DataBind();

        }

        /// <summary>
        /// 查询信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnChaXun_Click(object sender, EventArgs e)
        {
            LoadGridData();
        }

        


        #endregion

        #region 方法

        /// <summary>
        /// 初始化数据
        /// </summary>
        private void InitData()
        {
            int wtype = BusinessTool.WType;
            //gvList.SettingsPager.Mode = GridViewPagerMode.EndlessPaging;
            //水量年月
            int water_y_m = Convert.ToInt32(BusinessTool.GetSLYMorZWNY(1, wtype));
            for (int i = DateTime.Now.Year - 10; i <= DateTime.Now.Year; i++)
            {
                cmbYear.Items.Add(i.ToString(), i.ToString());
            }
            cmbYear.Value = water_y_m / 100;
            for (int i = 1; i <= 12; i++)
            {
                cmbMonth.Items.Add(i.ToString(), i);
            }
            cmbMonth.Value = water_y_m % 100;
           
            int userid = LoginInfo.CurrentUser.UserID;
            //if (userid == 111 || userid == 116 || userid == 99999 || userid == 117)
            //{
            //    //btnSave.Visible = true;
            //    //btnCancel.Visible = true;
            //}

        }



     

        /// <summary>
        /// 加载查询数据
        /// </summary>
        private void LoadGridData()
        {
            try
            {
                DataTable dt1 = _SpHelper.P_QSSF_HGTF_DR(1, null,null,null,null).Tables[0];
                dtGrid = dt1;
                gvList.DataSource = dtGrid;
                gvList.DataBind();
               // zjl.Text = "共" + gvList.VisibleRowCount + "条";
                gvList.FocusedRowIndex = -1;
                gvList.FocusedRowIndex = 0;


                //dtGrid2 = _SpHelper.P_QSSF_HGTF_DR(2, null, null, null).Tables[0];
                //gvList2.DataSource = dtGrid2;
                //gvList2.DataBind();
            }
            catch (Exception ex)
            {

            }
        }



        ///// <summary>
        ///// 前台表格行改变事件回调方法
        ///// </summary>
        ///// <param name="source"></param>
        ///// <param name="e"></param>
        protected void ASPxCallback1_Callback(object source, DevExpress.Web.CallbackEventArgs e)
        {
            int bh = Convert.ToInt32(this.hdCollection.Get("bh"));
            string msg = "";
            if (e.Parameter == "delete")
            {
                DataTable dt_temp = _SpHelper.P_QSSF_HGTF_DR(4, null, null, bh, null).Tables[0];
                if (dt_temp.Rows.Count>0)
                {
                    msg = dt_temp.Rows[0][0].ToString();
                }
            }

            e.Result = e.Parameter + "|0|" + msg;
        }



        protected void gvList_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e)
        {
            LoadGridData();
        }


        /// <summary>
        /// 孙雪婷 修改于2017-10-20
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void UpLoadFile_FileUploadComplete(object sender, FileUploadCompleteEventArgs e)
        {
            string year = this.hdCollection.Get("year").ToString();// "2017";// cmbYear.Text;
            string month = this.hdCollection.Get("month").ToString();// "12";// cmbMonth.Text;            
            e.CallbackData = "操作失败";
            int userid = LoginInfo.CurrentUser.UserID;
            

            const string UploadDirectory = "/UPLOAD/HGTF_DXS/";  //图片上传文件夹路径
            if (UpLoadFile.IsValid)
            {
               
                string fileName = year + "年" + month + "月地下水人工回灌预退费汇总表" + Path.GetExtension(UpLoadFile.FileName);  //文件名
                string filePath = Path.Combine(Server.MapPath(UploadDirectory), fileName);    //文件完整物理路径
                UpLoadFile.SaveAs(filePath);
                DataTable dt = ReadExcel(filePath);

                if (dt != null && dt.Rows.Count > 4)
                {
                    DataTable newdt = new DataTable();
                    newdt.Columns.Add("S_HUMING");//户名
                    newdt.Columns.Add("I_ZBYTSL");//至本月退水量
                    newdt.Columns.Add("I_YTSL");//已退水量
                    newdt.Columns.Add("I_BCTFSL");//本次退费水量
                    newdt.Columns.Add("N_SJ");//水价
                    newdt.Columns.Add("N_BCTSF");//本次退水费
                    newdt.Columns.Add("N_YTSF");//预退水费
                    newdt.Columns.Add("N_STSF");//实退水费
                    newdt.Columns.Add("I_YM");//年月

                    for (int i = 4; i < dt.Rows.Count; i++)
                    {
                        DataRow newdr = newdt.NewRow();
                        newdr["S_HUMING"] = dt.Rows[i][0];
                        newdr["I_ZBYTSL"] = dt.Rows[i][1];
                        newdr["I_YTSL"] = dt.Rows[i][2];
                        newdr["I_BCTFSL"] = dt.Rows[i][3];
                        newdr["N_SJ"] = dt.Rows[i][4];
                        newdr["N_BCTSF"] = dt.Rows[i][5];
                        newdr["N_YTSF"] = dt.Rows[i][6];
                        newdr["N_STSF"] = dt.Rows[i][7];
                        newdr["I_YM"] = Convert.ToInt32(year) * 100 + Convert.ToInt32(month);
                        newdt.Rows.Add(newdr);
                    }

                   
                    string tablename = "PLANWATER_TEMP.QS_SF_HGTF_TMP_DR";
                    if (BusinessTool.InsertOracelData(newdt, tablename))
                    {
                        DataSet ds = _SpHelper.P_QSSF_HGTF_DR(3, Convert.ToInt32(year), Convert.ToInt32(month),null, fileName);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                        {
                            e.IsValid = true;
                            e.CallbackData = "操作成功";
                        }

                    }
                }
            }
        }

        private DataTable ReadExcel(string ExcelFilePath)
        {
            DataTable dt = new DataTable();
            //string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" +ExcelFilePath + ";Extended Properties=Excel 8.0";
            string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + ExcelFilePath;
            OleDbConnection Connection = new OleDbConnection(strConnection);
            Connection.Open();
            string CommandText = "";
            DataTable dtsheetName = GetWorkBookName(Connection);
            if (dtsheetName != null && dtsheetName.Rows.Count > 0)
            {
                CommandText = "select * from [" + dtsheetName.Rows[0]["TABLE_NAME"].ToString() + "]";
            }
            else
            {
                return null;
            }
            OleDbCommand comm = new OleDbCommand(CommandText, Connection);

            try
            {

                OleDbDataAdapter da = new OleDbDataAdapter(comm);
                DataSet Ds = new DataSet();
                da.Fill(Ds);
                dt = Ds.Tables[0];
            }
            catch (Exception)
            {
            }
            finally
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
            }
            return dt;

        }
        /// <summary>
        /// 获取Excel中的所有工作簿
        /// </summary>
        /// <param name="Connection">OleDbConnection对象</param>
        /// <returns></returns>
        public static DataTable GetWorkBookName(OleDbConnection Connection)
        {
            DataTable Dt = null;
            try
            {
                if (Connection.State == ConnectionState.Closed) Connection.Open();
                Dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            }
            catch (Exception)
            {
            }
            finally
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
            }
            return Dt;
        }

        #endregion

       


        protected void btnExport_Click(object sender, EventArgs e)
        {
            
            string url = "\\UpLoad\\HGTF_DXS\\" + "";
        }

        protected void gvList2_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e)
        {
            //int year = Convert.ToInt32(cmbYear.Text);
            //int month = Convert.ToInt32(cmbMonth.Text);
            int bianhao = Convert.ToInt32(this.hdCollection.Get("I_BIANHAO"));
            dtGrid2 = _SpHelper.P_QSSF_HGTF_DR(2, null, null, bianhao, null).Tables[0];
            gvList2.DataSource = dtGrid2;
            gvList2.DataBind();
        }
    }
}